'''
    连接 PL/SQL 数据库
'''
import this

import cx_Oracle


class cxOracleDB:
    def __init__(self):
        self.username = "tb"
        self.password = "tb0851"
        self.host = "10.229.199.116"
        self.port = "1521"
        self.service_name = "sys"
        self.cursor = None
        self.connection = None
        self.connect()

    def connect(self):
        try:
            cx_Oracle.init_oracle_client(lib_dir="/Users/Environment/oracle/PLSQLDriver/instantclient_23_3")
            dsn = cx_Oracle.makedsn(self.host, self.port, service_name=self.service_name)
            print(dsn)
            self.connection = cx_Oracle.connect(self.username, self.password, dsn)
            print("Connected to Oracle database Success")
            self.cursor = self.connection.cursor()
        except Exception as e:
            print('''
            Connect error
                error info:''', e)
    def close(self):
        self.cursor.close()
        self.connection.close()
        return True

    '''
        设计咨询标数据
    '''
    def executeInsertSJZXBSJ(self, sql):
        data = f'''
            INSERT INTO CMP_DICT_SJZXBSJ_TEST(STAGE,KEYWORDS,REGION,PROJECT,RELEASEDATE,SCALE,LINK)
            VALUES {sql}
        '''
        try:
            self.cursor.execute(data)
            self.connection.commit()
            return True
        except Exception as e:
            print('数据插入失败',e)
        return True

    '''
        设计咨询标中标数据
        插入对应的数据
    '''
    def executeInsertSJZXBZBSJ(self, sql):
        self.cursor.execute(f'''
            INSERT INTO CMP_DICT_SJZXBZBSJ_TEST(STAGE,KEYWORDS,REGION,PROJECT,TENDERINGUNIT,RELEASEDATE,SCALE,LINK,STATUS,WININGUNIT,DATASTAMP)
            VALUES {sql}
        ''')
        return True

    '''
        中标数据
        插入对应的数据
    '''
    def executeInsertWinBid(self, sql):
        self.cursor.execute(f'''
            INSERT INTO CMP_DICT_WIN_DATA_TEST(STAGE,KEYWORDS,REGION,PROJECT,TENDERINGUNIT,RELEASEDATE,SCALE,LINK,STATUS,WININGUNIT,DATASTAMP)
            VALUES {sql}
        ''')
        return True

    '''
        设计咨询标数据
        查询对应的数据
    '''
    def queryData(self):
        try:
            self.cursor.execute("select * from CMP_DICT_SJZXBSJ")
            rows = self.cursor.fetchall()
            for row in rows:
                print(f"Row: {row}")
            return True
        except cx_Oracle.NotSupportedError as e:
            print(f"Error querying data: {e}")
            return False